package com.apes.enquiry.enquiry.service;

import com.alibaba.fastjson.JSONArray;
import com.alibaba.fastjson.JSONObject;
import com.apes.framework.query.ApesDao;
import com.apes.framework.util.DateUtil;
import com.apes.framework.util.MapUtil;
import org.springframework.stereotype.Component;

import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.List;
import java.util.Map;

/**
 * 类描述：
 *
 * @ClassName OriginalProductDao
 * @Description 描述
 * @Author wushilong
 * @Date 2023/3/16 11:23
 * @Version 1.0
 */
@Component("enquiryDao")
public class EnquiryDao extends ApesDao {


    public String getPagePrintSql(String sql, int start, int end) {

        String sPageHead = "SELECT PAGE.* FROM(SELECT TAB.*,ROWNUM AS NUM FROM (";
        String sPagetail = ") TAB WHERE ROWNUM <= "+end+" ) PAGE  WHERE PAGE.NUM >= "+start;

        return sPageHead + sql + sPagetail;
    }


    public JSONArray findAllOfferPage(JSONObject param, Integer pageNum, Integer pageSize) {
        String sql = "SELECT\n" +
                "       ROWNUM         rowno,\n" +
                "       A.ENQUIRY_ID    XJID,\n" +
                "       TO_CHAR(A.ENQUIRY_DATE, 'YYYY-MM-DD hh24:mi:ss')  XJDATE,\n" +
                "       A.QCCP          QCCP,\n" +
                "       A.STORE_NAME    MDNAME,\n" +
                "       A.CATEGORY_NAME XJPJ,\n" +
                "       B.PUSH          TD,\n" +
                "       B.ALREADY       YB,\n" +
                "       B.LACK          WH,\n" +
                "       B.SPOT          XH,\n" +
                "       B.BOOK          DH,\n" +
                "       B.ORIGINAL      YCBJ,\n" +
                "       B.BRAND         PPBJ,\n" +
                "       B.ORIG_SAME     TZBJ,\n" +
                "       B.OTHER         QTBJ,\n" +
                "       A.VIN           VIN,\n" +
                "       A.FW            FW,\n" +
                "       A.CP            CP,\n" +
                "       A.ZT            ZT,\n" +
                "       A.FP            FP,\n" +
                "       A.OE            OE,\n" +
                "       A.BJZ           BJZ,\n" +
                "       B.FLAG   \n" +
                "  FROM (SELECT A.ID ENQUIRY_ID,\n" +
                "               A.CREATE_DATE ENQUIRY_DATE,\n" +
                "               A.State ZT,\n" +
                "               A.BE_INVOICE FP,\n" +
                "               A.Create_Date,\n" +
                "               (SELECT NAME\n" +
                "                  FROM YYGYS.MAS_CAR_LABEL\n" +
                "                 WHERE ID = A.VEHICLE_LABEL) QCCP,\n" +
                "               (SELECT NAME FROM YYESB.MAS_DEPT WHERE ID = A.STORE) STORE_NAME,\n" +
                "               (SELECT NAME FROM YYESB.mas_dept_offer_group WHERE dept_id = A.STORE) BJZ,\n" +
                "               B.ID ITEM_ID,\n" +
                "               B.CATEGORY_NAME,\n" +
                "               B.OE,\n" +
                "               A.VIN,\n" +
                "               A.service_plan_id FW,\n" +
                "               A.car_license CP\n" +
                "          FROM YYGYS.SP_MAS_ENQUIRY A\n" +
                "          LEFT JOIN YYGYS.SP_MAS_ENQUIRY_ITEM B\n" +
                "            ON A.ID = B.ENQUIRY_ID  order by A.ID desc) A\n" +
                "  LEFT JOIN (SELECT ENQUIRY_ITEM_ID,\n" +
                "                    COUNT(DISTINCT SUPPLIER) PUSH,\n" +
                "                    COUNT(DISTINCT CASE\n" +
                "                            WHEN QUOTE_STATE = 'already' THEN\n" +
                "                             SUPPLIER\n" +
                "                            ELSE\n" +
                "                             NULL\n" +
                "                          END) ALREADY,\n" +
                "                    COUNT(DISTINCT CASE\n" +
                "                             WHEN QUOTE_STATE = 'lack' THEN\n" +
                "                              SUPPLIER\n" +
                "                             ELSE\n" +
                "                              NULL\n" +
                "                           END) LACK， COUNT(CASE\n" +
                "                      WHEN POD = 0 THEN\n" +
                "                       1\n" +
                "                      ELSE\n" +
                "                       NULL\n" +
                "                    END) SPOT,\n" +
                "                    COUNT(CASE\n" +
                "                            WHEN POD > 0 THEN\n" +
                "                             1\n" +
                "                            ELSE\n" +
                "                             NULL\n" +
                "                          END) BOOK,\n" +
                "                    COUNT(CASE\n" +
                "                            WHEN PZID = '001' THEN\n" +
                "                             1\n" +
                "                            ELSE\n" +
                "                             NULL\n" +
                "                          END) ORIGINAL,\n" +
                "                    COUNT(CASE\n" +
                "                            WHEN PZID = '003' THEN\n" +
                "                             1\n" +
                "                            ELSE\n" +
                "                             NULL\n" +
                "                          END) BRAND,\n" +
                "                    COUNT(CASE\n" +
                "                            WHEN PZID = '002' THEN\n" +
                "                             1\n" +
                "                            ELSE\n" +
                "                             NULL\n" +
                "                          END) ORIG_SAME,\n" +
                "                    COUNT(CASE\n" +
                "                            WHEN PZID NOT IN ('001', '002', '003') THEN\n" +
                "                             1\n" +
                "                            ELSE\n" +
                "                             NULL\n" +
                "                          END) OTHER,\n" +
                "                    CASE\n" +
                "                             WHEN COUNT(RESULT_ID) > 0 THEN\n" +
                "                              1\n" +
                "                             ELSE\n" +
                "                              0\n" +
                "                           END FLAG"+
                "               FROM (SELECT A.ENQUIRY_ITEM_ID,\n" +
                "                            B.ENQUIRY_ID,\n" +
                "                            B.PARTY_ROLE_ID SUPPLIER,\n" +
                "                            B.ID,\n" +
                "                            A.QUOTE_STATE,\n" +
                "                            B.STATE,\n" +
                "                            A.ID QUOTE_ITEM_ID,\n" +
                "                            C.ID RESULT_ID,\n"+
                "                            (SELECT PZID\n" +
                "                               FROM YYGYS.SP_MAS_PPXX\n" +
                "                              WHERE ID = C.QUOTE_BRAND) PZID,\n" +
                "                            C.STATE HY_STATE,\n" +
                "                            C.PLACE_OF_DISPATCH POD\n" +
                "                       FROM YYGYS.SP_MAS_QUOTE_ITEM A\n" +
                "                       LEFT JOIN YYGYS.SP_MAS_QUOTE B\n" +
                "                         ON A.QUOTE_ID = B.ID\n" +
                "                       LEFT JOIN YYGYS.SP_MAS_QUOTE_RESULT C\n" +
                "                         ON C.QUOTE_ID = A.QUOTE_ID\n" +
                "                        AND C.ENQUIRY_ITEM = A.ENQUIRY_ITEM_ID)\n" +
                "              GROUP BY ENQUIRY_ITEM_ID) B\n" +
                "    ON A.ITEM_ID = B.ENQUIRY_ITEM_ID\n" +
                "    where 1=1 " ;
        if (param.getString("XJID") != null && !"".equals(param.getString("XJID"))){
            sql += " AND A.ENQUIRY_ID = '" + param.getString("XJID") + "' ";
        }
        if (param.getString("MDNAME") != null && !"".equals(param.getString("MDNAME"))){
            sql += " AND A.STORE_NAME LIKE '%" + param.getString("MDNAME") + "%' ";
        }
        if (param.getString("FP") != null && !"".equals(param.getString("FP"))){
            sql += " AND A.FP = '" + param.getString("FP") + "' ";

        }
        if (param.getString("ZT") != null && !"".equals(param.getString("ZT"))){
            String ZT = "";
            if("报价中".equals(param.getString("ZT"))){
                ZT = "ing";
            }
            if("已完成".equals(param.getString("ZT"))){
                ZT = "finish";
            }if("已过期".equals(param.getString("ZT"))){
                ZT = "expired";
            }
            sql += " AND A.ZT = '" + ZT + "' ";
        }
        if (param.getString("VIN") != null && !"".equals(param.getString("VIN"))){
            sql += " AND A.VIN = '" + param.getString("VIN") + "' ";
        }

        if (param.getString("FW") != null && !"".equals(param.getString("FW"))){
            sql += " AND A.FW = '" + param.getString("FW") + "' ";
        }

        if (param.getString("CP") != null && !"".equals(param.getString("CP"))){
            sql += " AND A.CP = '" + param.getString("CP") + "' ";
        }

        if (param.getString("FLAG") != null && !"".equals(param.getString("FLAG"))){
            sql += " AND B.FLAG = '" + param.getString("FLAG") + "' ";
        }
        if (param.getJSONObject("offerGroup") != null){
            sql += " AND A.BJZ = '" + param.getJSONObject("offerGroup").getString("name") + "' ";
        }
        if(param.getString("XJDATE")!=null && param.getJSONArray("XJDATE").size()>0){
            JSONArray data = param.getJSONArray("XJDATE");
            SimpleDateFormat ft = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
            String startDate = ft.format(data.getDate(0));
            String finishDate = ft.format(data.getDate(1));
            sql += " AND A.ENQUIRY_DATE >= TO_DATE('" + startDate + "', 'YYYY-MM-DD HH24:MI:SS') AND A.ENQUIRY_DATE<=TO_DATE('" + finishDate + "', 'YYYY-MM-DD HH24:MI:SS') ";
        }
        sql += " order by A.ENQUIRY_DATE desc ";

        String SQL = getPagePrintSql(sql,pageNum,pageSize);

        return toJSONArray(find(SQL, param));
    }

    public JSONArray countOffer(JSONObject param) {

        String sql = "SELECT count(*) AS count FROM (   SELECT\n" +
                "       A.ENQUIRY_ID    ENQUIRY_ID,\n" +
                "       A.ENQUIRY_DATE  XJDATE,\n" +
                "       A.QCCP          QCCP,\n" +
                "       A.STORE_NAME    STORE_NAME,\n" +
                "       A.CATEGORY_NAME XJPJ,\n" +
                "       B.PUSH          TD,\n" +
                "       B.ALREADY       YB,\n" +
                "       B.LACK          WH,\n" +
                "       B.SPOT          XH,\n" +
                "       B.BOOK          DH,\n" +
                "       B.ORIGINAL      YCBJ,\n" +
                "       B.BRAND         PPBJ,\n" +
                "       B.ORIG_SAME     TZBJ,\n" +
                "       B.OTHER         QTBJ,\n" +
                "       A.VIN           VIN,\n" +
                "       A.FW           FW,\n" +
                "       A.CP           CP,\n" +
                "       A.ZT            ZT,\n" +
                "       A.FP            FP,\n" +
                "       A.CREATE_DATE   XJSJ,\n" +
                "       A.BJZ           BJZ,\n" +
                "       B.FLAG   \n" +
                "  FROM (SELECT A.ID ENQUIRY_ID,\n" +
                "               A.CREATE_DATE ENQUIRY_DATE,\n" +
                "               A.State ZT,\n" +
                "               A.BE_INVOICE FP,\n" +
                "               A.CREATE_DATE,\n" +
                "               (SELECT NAME\n" +
                "                  FROM YYGYS.MAS_CAR_LABEL\n" +
                "                 WHERE ID = A.VEHICLE_LABEL) QCCP,\n" +
                "               (SELECT NAME FROM YYESB.MAS_DEPT WHERE ID = A.STORE) STORE_NAME,\n" +
                "               B.ID ITEM_ID,\n" +
                "               B.CATEGORY_NAME,\n" +
                "               (SELECT NAME FROM YYESB.mas_dept_offer_group WHERE dept_id = A.STORE) BJZ,\n" +
                "               B.OE,\n" +
                "               A.VIN,\n" +
                "               A.service_plan_id FW,\n" +
                "               A.car_license CP\n" +
                "          FROM YYGYS.SP_MAS_ENQUIRY A\n" +
                "          LEFT JOIN YYGYS.SP_MAS_ENQUIRY_ITEM B\n" +
                "            ON A.ID = B.ENQUIRY_ID) A\n" +
                "  LEFT JOIN (SELECT ENQUIRY_ITEM_ID,\n" +
                "                    COUNT(DISTINCT SUPPLIER) PUSH,\n" +
                "                    COUNT(DISTINCT CASE\n" +
                "                            WHEN QUOTE_STATE = 'already' THEN\n" +
                "                             SUPPLIER\n" +
                "                            ELSE\n" +
                "                             NULL\n" +
                "                          END) ALREADY,\n" +
                "                    COUNT(DISTINCT CASE\n" +
                "                             WHEN QUOTE_STATE = 'lack' THEN\n" +
                "                              SUPPLIER\n" +
                "                             ELSE\n" +
                "                              NULL\n" +
                "                           END) LACK， COUNT(CASE\n" +
                "                      WHEN HY_STATE = '00' THEN\n" +
                "                       1\n" +
                "                      ELSE\n" +
                "                       NULL\n" +
                "                    END) SPOT,\n" +
                "                    COUNT(CASE\n" +
                "                            WHEN HY_STATE = '10' THEN\n" +
                "                             1\n" +
                "                            ELSE\n" +
                "                             NULL\n" +
                "                          END) BOOK,\n" +
                "                    COUNT(CASE\n" +
                "                            WHEN PZID = '001' THEN\n" +
                "                             1\n" +
                "                            ELSE\n" +
                "                             NULL\n" +
                "                          END) ORIGINAL,\n" +
                "                    COUNT(CASE\n" +
                "                            WHEN PZID = '003' THEN\n" +
                "                             1\n" +
                "                            ELSE\n" +
                "                             NULL\n" +
                "                          END) BRAND,\n" +
                "                    COUNT(CASE\n" +
                "                            WHEN PZID = '002' THEN\n" +
                "                             1\n" +
                "                            ELSE\n" +
                "                             NULL\n" +
                "                          END) ORIG_SAME,\n" +
                "                    COUNT(CASE\n" +
                "                            WHEN PZID NOT IN ('001', '002', '003') THEN\n" +
                "                             1\n" +
                "                            ELSE\n" +
                "                             NULL\n" +
                "                          END) OTHER,\n" +
                "                    CASE\n" +
                "                             WHEN COUNT(RESULT_ID) > 0 THEN\n" +
                "                              1\n" +
                "                             ELSE\n" +
                "                              0\n" +
                "                           END FLAG"+
                "               FROM (SELECT A.ENQUIRY_ITEM_ID,\n" +
                "                            B.ENQUIRY_ID,\n" +
                "                            B.PARTY_ROLE_ID SUPPLIER,\n" +
                "                            B.ID,\n" +
                "                            A.QUOTE_STATE,\n" +
                "                            B.STATE,\n" +
                "                            A.ID QUOTE_ITEM_ID,\n" +
                "                            C.ID RESULT_ID,\n"+
                "                            (SELECT PZID\n" +
                "                               FROM YYGYS.SP_MAS_PPXX\n" +
                "                              WHERE ID = C.QUOTE_BRAND) PZID,\n" +
                "                            C.STATE HY_STATE\n" +
                "                       FROM YYGYS.SP_MAS_QUOTE_ITEM A\n" +
                "                       LEFT JOIN YYGYS.SP_MAS_QUOTE B\n" +
                "                         ON A.QUOTE_ID = B.ID\n" +
                "                       LEFT JOIN YYGYS.SP_MAS_QUOTE_RESULT C\n" +
                "                         ON C.QUOTE_ID = A.QUOTE_ID\n" +
                "                        AND C.ENQUIRY_ITEM = A.ENQUIRY_ITEM_ID)\n" +
                "              GROUP BY ENQUIRY_ITEM_ID) B\n" +
                "    ON A.ITEM_ID = B.ENQUIRY_ITEM_ID) A WHERE 1=1  ";
        if (param.getString("XJID") != null && !"".equals(param.getString("XJID"))){
            sql += " AND A.ENQUIRY_ID = '" + param.getString("XJID") + "'";
        }
        if (param.getString("MDNAME") != null && !"".equals(param.getString("MDNAME"))){
            sql += " AND A.STORE_NAME LIKE '%" + param.getString("MDNAME") + "%'";
        }
        if (param.getString("FP") != null && !"".equals(param.getString("FP"))){
            sql += " AND A.FP = '" + param.getString("FP") + "'";

        }
        if (param.getString("ZT") != null && !"".equals(param.getString("ZT"))){
            String ZT = "";
            if("报价中".equals(param.getString("ZT"))){
                ZT = "ing";
            }
            if("已完成".equals(param.getString("ZT"))){
                ZT = "finish";
            }if("已过期".equals(param.getString("ZT"))){
                ZT = "expired";
            }
            sql += " AND A.ZT = '" + ZT + "'";
        }
        if (param.getString("VIN") != null && !"".equals(param.getString("VIN"))){
            sql += " AND A.VIN = '" + param.getString("VIN") + "'";
        }
        if (param.getString("FW") != null && !"".equals(param.getString("FW"))){
            sql += " AND A.FW = '" + param.getString("FW") + "' ";
        }
        if (param.getString("CP") != null && !"".equals(param.getString("CP"))){
            sql += " AND A.CP = '" + param.getString("CP") + "' ";
        }
        if (param.getString("FLAG") != null && !"".equals(param.getString("FLAG"))){
            sql += " AND A.FLAG = '" + param.getString("FLAG") + "' ";
        }
        if (param.getJSONObject("offerGroup") != null){
            sql += " AND A.BJZ = '" + param.getJSONObject("offerGroup").getString("name") + "' ";
        }
        if(param.getString("XJDATE")!=null && param.getJSONArray("XJDATE").size()>0) {
            JSONArray data = param.getJSONArray("XJDATE");
            SimpleDateFormat ft = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
            String startDate = ft.format(data.getDate(0));
            String finishDate = ft.format(data.getDate(1));
            sql += " AND A.XJSJ >= TO_DATE('" + startDate + "', 'YYYY-MM-DD HH24:MI:SS') AND A.XJSJ<=TO_DATE('" + finishDate + "', 'YYYY-MM-DD HH24:MI:SS')";

        }
        return toJSONArray(find(sql, param));
    }


}
